# 用python读取每个飞机的所有轨迹信息，并计算出每个飞机的飞行高度变化趋势，速度变化趋势，判断飞机是起飞还是降落
import mysql.connector
import numpy as np
from scipy import stats
from datetime import datetime, timedelta
import math
from math import radians, sin, cos, sqrt, asin


# MySQL数据库配置
config = {
    'user': 'root',
    'password': '123456',
    'host': '192.168.124.8',
    'database': 'vanna_ai',
    'port': '3333',
}


# 查询特定飞机的轨迹信息
def select_aircraft_trace(icao, reg_id, cursor):
    """
    查询数据库中特定飞机的轨迹信息。

    参数:
    icao (str): 飞机的ICAO代码。
    reg_id (str): 飞机的注册ID。
    cursor (mysql.connector.cursor.MySQLCursor): 数据库游标。

    返回:
    tuple: 包含飞机轨迹信息的元组，每个元素是一个包含轨迹信息的字典。
    """
    query = "SELECT currenttime, latitude, longitude,altitude,velocity FROM civil_aircraft_trace WHERE icao = %s AND reg_id = %s"
    cursor.execute(query, (icao, reg_id))
    rows = cursor.fetchall()
    column_names = [desc[0] for desc in cursor.description]
    result = [dict(zip(column_names, row)) for row in rows]
    return tuple(result)


# 根据飞机轨迹数据判断飞机是起飞还是降落
def find_take_and_land(trace_data):
    """
    分析飞机轨迹数据，判断飞机是起飞还是降落。
    计算方式： 起飞高度从0递增，速度从0递增       降落递减到0

    参数:
    trace_data (tuple): 飞机轨迹数据，包含飞行高度、速度等信息。
    """
    def get_formatted_dates(current_time):
        """生成当前时间及前后1小时、2小时的格式化日期"""
        dt = datetime.fromtimestamp(current_time)
        adjusted_dt_1 = dt - timedelta(hours=1)
        adjusted_dt_2 = dt - timedelta(hours=2)
        return (
            dt.strftime('%Y-%m-%d %H'),
            adjusted_dt_1.strftime('%Y-%m-%d %H'),
            adjusted_dt_2.strftime('%Y-%m-%d %H')
        )

    def analyze_trend(data):
        """分析趋势，返回上升或下降"""
        if not data or len(data) < 2:
            return {'trend': 'unknown'}
        diff = [data[i + 1] - data[i] for i in range(len(data) - 1)]
        if all(d > 0 for d in diff):
            return {'trend': 'ascending'}
        elif all(d < 0 for d in diff):
            return {'trend': 'descending'}
        else:
            return {'trend': 'unknown'}

    def get_velocity_trends(points):
        """获取速度趋势"""
        velocities = [point['velocity'] for point in points]
        return analyze_trend(velocities)

    processed = {}
    for i in range(len(trace_data)):
        if trace_data[i]['altitude'] != 'ground':
            continue

        current_point = trace_data[i]
        formatted_date, formatted_date_1, formatted_date_2 = get_formatted_dates(current_point['currenttime'])

        # 处理起点
        key_start = f'{formatted_date}起点'
        if i <= 5 and key_start not in processed:
            processed[key_start] = trace_data[i]
            continue

        # 获取前后5个点
        previous_points = trace_data[max(0, i - 5):i]
        next_points = trace_data[i + 1:min(len(trace_data), i + 6)]

        if len(previous_points) < 5 or len(next_points) < 5:
            continue  # 数据不足，跳过

        # 分析趋势
        previous_result = get_velocity_trends(previous_points)
        next_result = get_velocity_trends(next_points)


        # 思路不对，应该先判断当前时间前两个小时之内是否有升点，再判断是否为升点
        # 如果当前时间前五个点是降落趋势，那么就先找到前面是否有

        # 判断是否为降点
        # if previous_result['trend'] == 'descending' and next_result['trend'] == 'descending':
        if previous_result['trend'] == 'descending':
            key_landing = f'{formatted_date}降点'
            if formatted_date_1 + '降点' in processed or formatted_date_2 + '降点' in processed:
                continue
            processed[key_landing] = trace_data[i]
        # 判断是否为起点
        # elif previous_result['trend'] == 'ascending' and next_result['trend'] == 'ascending':
        elif previous_result['trend'] == 'ascending':
            if formatted_date_1 + '起点' in processed or formatted_date_2 + '起点' in processed:
                continue
            processed[key_start] = trace_data[i]

    return processed


# 根据起降点查找机场信息
def find_airport_info(take_and_land_data, cursor):
    result_data = {}
    # 根据起降点查找机场信息
    # 先查询出所有机场信息
    cursor.execute("SELECT * FROM global_airports")
    airports = cursor.fetchall()
    for key, data in take_and_land_data.items():
        airport_aircraft_data = match_airport(data['latitude'], data['longitude'], airports)
        result_data[key] = airport_aircraft_data
    return result_data

# 根据坐标和机场信息，计算两点间的距离
def match_airport(point_lat, point_lon, airports, max_km=5):
    """匹配目标机场"""
    matched = []
    # 预筛范围（±0.045°对应约5公里）
    lat_range = 0.045
    lon_range = 0.045 / math.cos(math.radians(point_lat))  # 修正经度范围

    for airport in airports:
        airport_lat = float(airport[6])
        airport_lon = float(airport[7])
        # 1. 范围预筛
        if (abs(airport_lat - point_lat) <= lat_range) and (abs(airport_lon - point_lon) <= lon_range):
            # 2. 精确计算
            distance = haversine(point_lat, point_lon, airport_lat, airport_lon)
            if distance <= max_km:
                matched.append((airport[1], distance))
    # 返回最近机场（若多个匹配）
    return sorted(matched, key=lambda x: x[1]) if matched else None


# 计算两点间球面距离（公里）
def haversine(lat1, lon1, lat2, lon2):
    """计算两点间球面距离（公里）"""
    R = 6371  # 地球半径（公里）
    dlat = radians(lat2 - lat1)
    dlon = radians(lon2 - lon1)
    a = sin(dlat/2)**2 + cos(radians(lat1)) * cos(radians(lat2)) * sin(dlon/2)**2
    c = 2 * asin(sqrt(a))
    return R * c

# 主函数，用于读取所有飞机实体并分析其轨迹数据
def main():
    """
    主函数，用于读取数据库中的所有飞机实体，并分析每个飞机的轨迹数据。
    """
    # 连接MySQL数据库
    connection = mysql.connector.connect(**config)
    cursor = connection.cursor()
    # 查询数据库中的所有飞机实体
    cursor.execute("SELECT icao,reg_id FROM civil_aircraft")
    rows = cursor.fetchall()
    for row in rows:
        # 读取飞机实体的信息
        # 飞机icao
        icao = row[0]
        # 飞机reg_id注册码
        reg_id = row[1]
        # 根据读取的每个飞机信息去轨迹表中查找对应的轨迹信息
        # 测试用
        # icao = '840563'
        # reg_id = 'JA01FD'
        trace_data = select_aircraft_trace(icao, reg_id, cursor)
        # 处理 trace_data
        take_and_land_data = find_take_and_land(trace_data)
        airport_info_data = find_airport_info(take_and_land_data, cursor)
        write_airport_info_to_db(icao, reg_id, airport_info_data, cursor,connection)
        # 分析完毕起降点后，根据起降点查找对应的机场信息


# 计算数据趋势
def analyze_trend(data, threshold=0.2):
    """
    分析数据趋势，允许一定比例的波动

    Args:
        data (list): 输入数据列表
        threshold (float): 允许的波动比例（默认20%）

    Returns:
        dict: 包含趋势类型、斜率、波动点索引等信息的字典
    """
    # 计算线性趋势斜率
    x = np.arange(len(data))
    slope, _, _, _, _ = stats.linregress(x, data)

    # Cox-Stuart趋势检验
    n = len(data)
    half = n // 2
    pairs = [(data[i], data[i + half]) for i in range(half)]
    increases = sum(1 for a, b in pairs if a < b)
    decreases = sum(1 for a, b in pairs if a > b)
    result = stats.binomtest(max(increases, decreases), n=half, p=0.5)
    p_value = result.pvalue

    # 统计波动点数量
    diffs = np.diff(data)
    up_count = sum(1 for d in diffs if d > 0)
    down_count = sum(1 for d in diffs if d < 0)
    fluctuation_ratio = min(up_count, down_count) / len(diffs)

    # 综合判断趋势

    if slope < 0 and fluctuation_ratio <= threshold:
        trend = "descending"
    elif slope > 0 and fluctuation_ratio <= threshold:
        trend = "ascending"

    # 获取波动点索引
    if slope < 0:
        fluctuation_points = np.where(diffs > 0)[0].tolist()
    else:
        fluctuation_points = np.where(diffs < 0)[0].tolist()

    return {
        "trend": trend, # 该字段主要返回趋势类型，如"ascending"、"descending"等  直接用这两个结果来作比较就行
        "slope": slope,
        "p_value": p_value,
        "fluctuation_points": fluctuation_points
    }


# 将起降点匹配到的机场信息写入数据库
def write_airport_info_to_db(icao, reg_id, airport_info, cursor, connection):
    """
    Write airport
    :param icao:
    :param reg_id:
    :param airport_info:
    :param cursor:
    :return:
    """
    # 入库之前需要解析数据，重要字段为：reg_id,icao,airport_name,take_and_load_info,distance
    #                   字段对应含义          飞机机场名称      起降点信息（时间）    起降点和机场距离
    print('开始入库')
    for key, value in airport_info.items():
        take_and_load_info = key
        if value is not None:
            airport_name, distance = value[0]
            # 将起降点匹配到的机场信息写入数据库
            cursor.execute("INSERT INTO civil_aircraft_take_and_land (icao,reg_id,airport_name,take_and_load_info,distance) VALUES (%s,%s,%s,%s,%s)",
                           (icao, reg_id, airport_name, take_and_load_info, distance))
            connection.commit()
        else:
            cursor.execute(
                "INSERT INTO civil_aircraft_take_and_land (icao,reg_id,airport_name,take_and_load_info,distance) VALUES (%s,%s,%s,%s,%s)",
                (icao, reg_id, '', take_and_load_info, None)
                )
            connection.commit()





# 程序入口
if __name__ == "__main__":
    main()
